﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using Model;
using Utility;
namespace Service
{
    /// <summary>
    /// 功能：HW_OrgUser类数据访问
    /// 创建人：Wilson
    /// 创建时间：2013-1-30   
    /// </summary>
    public class HW_OrgUserService : IDisposable
    {
        #region 公共
        private System.ComponentModel.Component component = new System.ComponentModel.Component();

        private bool disposed = false;

        ~HW_OrgUserService()
        {
            Dispose(false);
        }

        #endregion

        #region IDisposable 成员

        public void Dispose()
        {
            Dispose(true);
            GC.SuppressFinalize(this);
        }

        private void Dispose(bool disposing)
        {
            if (!this.disposed)
            {

                if (disposing)
                {

                    component.Dispose();
                }

                disposed = true;

            }
        }
        #endregion
		#region##MapperHW_OrgUser
        public IList<HW_OrgUser> MapperList(SqlDataReader reader)
         {
            IList<HW_OrgUser> list = new List<HW_OrgUser>();
            HW_OrgUser obj=null;
            while (reader.Read())
            {
                obj = new HW_OrgUser();
                obj.XX_UserName = (string)reader["XX_UserName"];
                obj.OrgCode = (string)reader["OrgCode"];
                obj.RoleId = reader["RoleId"] as int?;
                obj.XX_Pass = reader["XX_Pass"] as string;
                obj.CNName = reader["CNName"] as string;
                obj.ErrorTimes = reader["ErrorTimes"] as int?;
                obj.LastLoginTime = reader["LastLoginTime"] as DateTime?;
                //obj.Version = reader["Version"] as int?;
                list.Add(obj);
            }
            return list;
         }
        public HW_OrgUser Mapper(SqlDataReader reader)
         {
            HW_OrgUser obj=null;
            if (reader.Read())
            {
                obj = new HW_OrgUser();
                obj.XX_UserName = (string)reader["XX_UserName"];
                obj.OrgCode = (string)reader["OrgCode"];
                obj.RoleId = reader["RoleId"] as int?;
                obj.XX_Pass = reader["XX_Pass"] as string;
                obj.CNName = reader["CNName"] as string;
                obj.ErrorTimes = reader["ErrorTimes"] as int?;
                obj.LastLoginTime = reader["LastLoginTime"] as DateTime?;
                //obj.Version = reader["Version"] as int?;


                HW_Org org = new HW_Org();
                org.OrgCode = (string)reader["OrgCode"];
                org.DistrictCode = (string)reader["DistrictCode"];
                org.OrgName = (string)reader["OrgName"];
                //org.Version = (int)reader["Version"];
                org.CityId = (string)reader["CityId"];



                HW_OrgDetail orgDetail = new HW_OrgDetail();
                orgDetail.OrgCode = (string)reader["OrgCode"];
                orgDetail.Address = reader["Address"] as string;
                orgDetail.Email = reader["Email"] as string;
                orgDetail.MobilePhone = reader["MobilePhone"] as string;
                orgDetail.OfficePhone = reader["OfficePhone"] as string;
                orgDetail.ZipCode = reader["ZipCode"] as string;
                //orgDetail.Version = (int)reader["Version"];
                orgDetail.Fax = reader["Fax"] as string;
                orgDetail.LinkMan = reader["LinkMan"] as string;
                
                    org.hw_OrgDetail = orgDetail;

                    obj.hw_Org = org;
            }
            return obj;
         }
        #endregion
        #region##通过主键ID得到HW_OrgUser
        /// <summary>
        /// 功能：通过ID得到HW_OrgUser
        /// 创建人：  Wilson 
        /// 创建时间：2013-1-30   
        /// </summary>
        /// <param name="">主键ID</param>
        /// <returns></returns>
        public HW_OrgUser GetById(string xx_username)
        {            
            string sql = @"select * from HW_OrgUser a inner join HW_Org b on a.OrgCode=b.OrgCode
                           left join HW_OrgDetail c on b.OrgCode=c.OrgCode where a.XX_UserName=@userName";
            SqlParameter[] sp = new SqlParameter[]
            {
                new SqlParameter("@userName",xx_username)
            };
            
            SqlDataReader reader=SqlHelper.ExecuteReader(WebConn.connString,CommandType.Text,sql,sp);
            return Mapper(reader);
        }
        public IList<HW_OrgUser> GetByOrgCode(string orgcode)
        {
            string sql = @"select * from HW_OrgUser a inner join HW_Org b on a.OrgCode=b.OrgCode
                           left join HW_OrgDetail c on b.OrgCode=c.OrgCode where a.orgcode=@orgcode";
            SqlParameter[] sp = new SqlParameter[]
            {
                new SqlParameter("@orgcode",orgcode)
            };

            SqlDataReader reader = SqlHelper.ExecuteReader(WebConn.connString, CommandType.Text, sql, sp);
            return MapperList(reader);
        }
        #endregion
		
		#region##通过主键ID删除HW_OrgUser
        /// <summary>
        /// 功能：通过主键ID删除HW_OrgUser
        /// 创建人：  Wilson 
        /// 创建时间：2013-1-30   
        /// </summary>
        /// <param name="">主键ID</param>
        /// <returns></returns>
		public int DeleteById(string xx_username)
		{
			int i = 0;
			string sql = "delete from HW_OrgUser where XX_UserName = @PK";
            SqlParameter[] sp = new SqlParameter[]
            {
                new SqlParameter("@PK",xx_username)
            };
            i= SqlHelper.ExecuteNonQuery(WebConn.connString,CommandType.Text,sql,sp);
			return i;
		}
        /// <summary>
        /// 功能：通过主键ID删除HW_OrgUser
        /// 创建人：  Wilson 
        /// 创建时间：2013-1-30   
        /// </summary>
        /// <param name="">主键ID</param>
        /// <returns></returns>
		public int DeleteById(SqlTransaction transaction,string xx_username)
		{
			int i = 0;
			string sql = "delete from HW_OrgUser where XX_UserName = @PK";
            SqlParameter[] sp = new SqlParameter[]
            {
                new SqlParameter("@PK",xx_username)
            };
            i= SqlHelper.ExecuteNonQuery(transaction,CommandType.Text,sql,sp);
			return i;
		}
		#endregion	
		/// <summary>
		/// 最大的机构编码
		/// </summary>
		/// <param name="dis"></param>
		/// <returns></returns>
        public string GetMaxUser(string dis)
        {
            string sql = "select max(OrgCode) from HW_Org where OrgCode like @dis";
            SqlParameter[] sp = new SqlParameter[]
            {
                new SqlParameter("@dis",dis+"%")
            };
            object o= SqlHelper.ExecuteScalar(WebConn.connString, CommandType.Text, sql, sp);
            return o.ToString();
        }
        /// <summary>
        /// 最大用户
        /// </summary>
        /// <param name="orgcode"></param>
        /// <returns></returns>
        public string GetMaxUserName(string orgcode)
        {
            string sql = "select max(XX_UserName) from HW_OrgUser where OrgCode=@orgcode";
            SqlParameter[] sp = new SqlParameter[]
            {
                new SqlParameter("@orgcode",orgcode)
            };
            object o = SqlHelper.ExecuteScalar(WebConn.connString, CommandType.Text, sql, sp);
            return o.ToString();
        }

        #region##添加HW_OrgUser
        /// <summary>
        /// 功能：添加HW_OrgUser
        /// 创建人：  Wilson 
        /// 创建时间：2013-1-30
        /// </summary>
        /// <param name=""></param>
        /// <returns></returns>
        public int Add(HW_OrgUser hw_orguser)
        {
            int result = 0;
            string sql = "insert into HW_OrgUser(XX_UserName,OrgCode,RoleId,XX_Pass,CNName,ErrorTimes,LastLoginTime,Version) values(@XX_UserName,@OrgCode,@RoleId,@XX_Pass,@CNName,@ErrorTimes,@LastLoginTime,@Version)";			
            SqlParameter[] sp = new SqlParameter[]
            {
				new SqlParameter("@XX_UserName",hw_orguser.XX_UserName),
				new SqlParameter("@OrgCode",hw_orguser.OrgCode),
				new SqlParameter("@RoleId",hw_orguser.RoleId),
				new SqlParameter("@XX_Pass",hw_orguser.XX_Pass),
				new SqlParameter("@CNName",hw_orguser.CNName),
				new SqlParameter("@ErrorTimes",hw_orguser.ErrorTimes),
				new SqlParameter("@LastLoginTime",hw_orguser.LastLoginTime),
				new SqlParameter("@Version",hw_orguser.Version)
            };
            result = SqlHelper.ExecuteNonQuery(WebConn.connString,CommandType.Text,sql,sp);
            return result;
        }
         /// <summary>
        /// 功能：添加HW_OrgUser
        /// 创建人：  Wilson 
        /// 创建时间：2013-1-30
        /// </summary>
        /// <param name=""></param>
        /// <returns></returns>
        public int Add(SqlTransaction transaction,HW_OrgUser hw_orguser)
        {
            int result = 0;
            string sql = "insert into HW_OrgUser(XX_UserName,OrgCode,RoleId,XX_Pass,CNName,ErrorTimes,LastLoginTime,Version) values(@XX_UserName,@OrgCode,@RoleId,@XX_Pass,@CNName,@ErrorTimes,@LastLoginTime,@Version)";			
            SqlParameter[] sp = new SqlParameter[]
            {
				new SqlParameter("@XX_UserName",hw_orguser.XX_UserName),
				new SqlParameter("@OrgCode",hw_orguser.OrgCode),
				new SqlParameter("@RoleId",hw_orguser.RoleId),
				new SqlParameter("@XX_Pass",hw_orguser.XX_Pass),
				new SqlParameter("@CNName",hw_orguser.CNName),
				new SqlParameter("@ErrorTimes",hw_orguser.ErrorTimes),
				new SqlParameter("@LastLoginTime",hw_orguser.LastLoginTime),
				new SqlParameter("@Version",hw_orguser.Version)
            };
            result = SqlHelper.ExecuteNonQuery(transaction,CommandType.Text,sql,sp);
            return result;
        }
        #endregion
		
		#region##得到HW_OrgUser集合
		/// <summary>
        /// 功能：得到HW_OrgUser集合
        /// 创建人：  Wilson 
        /// 创建时间：2013-1-30
        /// </summary>       
        /// <returns></returns>
		public IList<HW_OrgUser> GetList()
		{
			string sql = "select * from HW_OrgUser";			
			SqlDataReader reader = SqlHelper.ExecuteReader(WebConn.connString,CommandType.Text,sql);
            return MapperList(reader);
		}
        /// <summary>
        /// 功能：得到HW_OrgUser集合
        /// 创建人：  Wilson 
        /// 创建时间：2013-1-30
        /// </summary>       
        /// <returns></returns>
//        public DataTable GetList(int pageIndex,int pageSize,out int count)
//        {
//            string sql = @"SELECT TOP (@pageSize) XX_UserName,OrgName,address,linkman,OfficePhone,e.name cityname,c.name districtname FROM dbo.HW_Org a LEFT JOIN dbo.HW_OrgUser b ON a.OrgCode=b.OrgCode LEFT
//                            JOIN dbo.HW_District c ON a.DistrictCode=c.DistrictCode LEFT JOIN dbo.HW_OrgDetail d
//                            ON a.OrgCode=d.OrgCode INNER JOIN dbo.HW_District e ON e.DistrictCode=a.CityId where XX_UserName not in (select top
//                            (@pageSize*(@pageIndex)) XX_UserName FROM HW_OrgUser ORDER BY XX_UserName)ORDER BY XX_UserName ";

//            SqlParameter[] sp = new SqlParameter[]
//            {       
//                new SqlParameter("@pageIndex",pageIndex),
//                new SqlParameter("@pageSize",pageSize),
//            };


//            //string sql = @"SELECT TOP @pageSize * FROM HW_OrgUser WHERE XX_UserName NOT IN (SELECT TOP @pageSiz*(*pageIndex-1) XX_UserName FROM HW_OrgUser ORDER BY XX_UserName)ORDER BY XX_UserName";
//            count = SelectCountById();
//            return SqlHelper.ExecuteDataset(WebConn.connString, CommandType.Text, sql, sp).Tables[0];
//            //SqlDataReader reader = SqlHelper.ExecuteReader(WebConn.connString, CommandType.Text, sql,sp);
//            //return MapperList(reader);
//        }
		#endregion
		
		#region##修改HW_OrgUser
        /// <summary>
        /// 功能：修改HW_OrgUser
        /// 创建人：  Wilson 
        /// 创建时间：2013-1-30
        /// </summary>
        /// <param name=""></param>
        /// <returns></returns>
        public int ModHW_OrgUser(HW_OrgUser hw_orguser)
        {
            int result = 0;
            string sql = "update HW_OrgUser set XX_UserName = @XX_UserName,OrgCode = @OrgCode,RoleId = @RoleId,XX_Pass = @XX_Pass,CNName = @CNName,ErrorTimes = @ErrorTimes,LastLoginTime = @LastLoginTime,Version = @Version where XX_UserName = @XX_UserName";
            SqlParameter[] sp = new SqlParameter[]
            {       
				new SqlParameter("@XX_UserName",hw_orguser.XX_UserName),
				new SqlParameter("@OrgCode",hw_orguser.OrgCode),
				new SqlParameter("@RoleId",hw_orguser.RoleId),
				new SqlParameter("@XX_Pass",hw_orguser.XX_Pass),
				new SqlParameter("@CNName",hw_orguser.CNName),
				new SqlParameter("@ErrorTimes",hw_orguser.ErrorTimes),
				new SqlParameter("@LastLoginTime",hw_orguser.LastLoginTime),
				new SqlParameter("@Version",hw_orguser.Version)
            };
            result = SqlHelper.ExecuteNonQuery(WebConn.connString,CommandType.Text,sql,sp);
            return result;
        }
         /// <summary>
        /// 功能：修改HW_OrgUser
        /// 创建人：  Wilson 
        /// 创建时间：2013-1-30
        /// </summary>
        /// <param name=""></param>
        /// <returns></returns>
        public int ModHW_OrgUser(SqlTransaction transaction,HW_OrgUser hw_orguser)
        {
            int result = 0;
            string sql = "update HW_OrgUser set XX_UserName = @XX_UserName,OrgCode = @OrgCode,RoleId = @RoleId,XX_Pass = @XX_Pass,CNName = @CNName,ErrorTimes = @ErrorTimes,LastLoginTime = @LastLoginTime,Version = @Version where XX_UserName = @XX_UserName";
            SqlParameter[] sp = new SqlParameter[]
            {       
				new SqlParameter("@XX_UserName",hw_orguser.XX_UserName),
				new SqlParameter("@OrgCode",hw_orguser.OrgCode),
				new SqlParameter("@RoleId",hw_orguser.RoleId),
				new SqlParameter("@XX_Pass",hw_orguser.XX_Pass),
				new SqlParameter("@CNName",hw_orguser.CNName),
				new SqlParameter("@ErrorTimes",hw_orguser.ErrorTimes),
				new SqlParameter("@LastLoginTime",hw_orguser.LastLoginTime),
				new SqlParameter("@Version",hw_orguser.Version)
            };
            result = SqlHelper.ExecuteNonQuery(transaction,CommandType.Text,sql,sp);
            return result;
        }
        #endregion
		
		#region##统计HW_OrgUser
		/// <summary>
        /// 修改HW_OrgUser
        /// 创建人：  Wilson 
        /// 创建时间：2013-1-30
        /// </summary>
        /// <param name=""></param>
        /// <returns></returns>
		public int SelectCount()
		{
			int i = 0;
            string sql = "select count(*) from HW_OrgUser";
            i = (int)SqlHelper.ExecuteScalar(WebConn.connString,CommandType.Text,sql);
            return i;
		}
		/// <summary>
        /// 修改HW_OrgUser
        /// 创建人：  Wilson 
        /// 创建时间：2013-1-30
        /// </summary>
        /// <param name=""></param>
        /// <returns></returns>
        public int SelectCountById()
        {
            int i=0;
            string sql="select count(XX_UserName) from HW_OrgUser";
            i = (int)SqlHelper.ExecuteScalar(WebConn.connString,CommandType.Text,sql);
            return i;
        }
		#endregion
        #region##自定义HW_OrgUser
        public DataTable GetListByDistrict(int pageIndex, int pageSize, out int count,string districtCode)
        {
            string sql=@"select Top(@pageSize) * from HW_OrgUser a inner join HW_Org b on a.OrgCode=b.OrgCode left join HW_OrgDetail c on a.OrgCode=c.OrgCode
                        inner join HW_District d on b.DistrictCode=d.DistrictCode inner join  HW_District e on d.ParentDistrictCode=e.DistrictCode
                        where b.DistrictCode=@districtCode and a.XX_UserName not in (select TOP(@pageSize*@pageIndex) XX_UserName from HW_OrgUser m inner join HW_Org n on
                        m.OrgCode=n.OrgCode where m.DistrictCode=@districtCode order by m.XX_UserName)order by XX_UserName
                        ";
            SqlParameter[] sp = new SqlParameter[]
            {       
				new SqlParameter("@pageIndex",pageIndex),
				new SqlParameter("@pageSize",pageSize),
				new SqlParameter("@districtCode",districtCode),
            };
            string sql2=@"select count(XX_UserName) from HW_OrgUser m inner join HW_Org n on
                        m.OrgCode=n.OrgCode order by XX_UserName where m.DistrictCode=@districtCode";
             SqlParameter[] sp2 = new SqlParameter[]
            {       
				new SqlParameter("@districtCode",districtCode),
            };
            count=(int)SqlHelper.ExecuteScalar(WebConn.connString,CommandType.Text,sql2,sp2);
            DataTable result = SqlHelper.ExecuteDataset(WebConn.connString,CommandType.Text,sql,sp).Tables[0];
            return result;
        }
        public DataTable GetListByCity(int pageIndex, int pageSize, out int count,string cityCode)
        {
            string sql=@"select Top(@pageSize) * from HW_OrgUser a inner join HW_Org b on a.OrgCode=b.OrgCode left join HW_OrgDetail c on a.OrgCode=c.OrgCode
                        inner join HW_District d on b.DistrictCode=d.DistrictCode inner join  HW_District e on d.ParentDistrictCode=e.DistrictCode
                        where b.CityCode=@cityCode and a.XX_UserName not in (select TOP(@pageSize*@pageIndex) XX_UserName from HW_OrgUser m inner join HW_Org n on
                        m.OrgCode=n.OrgCode where m.cityCode=@cityCode order by m.XX_UserName)order by XX_UserName
                        ";
            SqlParameter[] sp = new SqlParameter[]
            {       
				new SqlParameter("@pageIndex",pageIndex),
				new SqlParameter("@pageSize",pageSize),
				new SqlParameter("@cityCode",cityCode),
            };
            string sql2=@"select count(XX_UserName) from HW_OrgUser m inner join HW_Org n on
                        m.OrgCode=n.OrgCode order by XX_UserName where m.cityCode=@cityCode";
             SqlParameter[] sp2 = new SqlParameter[]
            {       
				new SqlParameter("@cityCode",cityCode),
            };
            count=(int)SqlHelper.ExecuteScalar(WebConn.connString,CommandType.Text,sql2,sp2);
            DataTable result = SqlHelper.ExecuteDataset(WebConn.connString,CommandType.Text,sql,sp).Tables[0];
            return result;
        }
        public DataTable GetList(int pageIndex, int pageSize, out int count)
        {
            string sql=@"select Top(@pageSize) * from HW_OrgUser a inner join HW_Org b on a.OrgCode=b.OrgCode left join HW_OrgDetail c on a.OrgCode=c.OrgCode
                        inner join HW_District d on b.DistrictCode=d.DistrictCode inner join  HW_District e on d.ParentDistrictCode=e.DistrictCode
                        where a.XX_UserName not in (select TOP(@pageSize*@pageIndex) XX_UserName from HW_OrgUser m inner join HW_Org n on
                        m.OrgCode=n.OrgCode order by m.XX_UserName)order by XX_UserName
                        ";
            SqlParameter[] sp = new SqlParameter[]
            {       
				new SqlParameter("@pageIndex",pageIndex),
				new SqlParameter("@pageSize",pageSize),
            };
            string sql2=@"select count(XX_UserName) from HW_OrgUser m inner join HW_Org n on
                        m.OrgCode=n.OrgCode";
            count=(int)SqlHelper.ExecuteScalar(WebConn.connString,CommandType.Text,sql2);
            DataTable result = SqlHelper.ExecuteDataset(WebConn.connString,CommandType.Text,sql,sp).Tables[0];
            return result;
        }
        /// <summary>
        /// 到到用户所有授权可看到的机构用户
        /// </summary>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="count"></param>
        /// <param name="pdistrictCode"></param>
        /// <returns></returns>
        public DataTable GetSubList(int pageIndex, int pageSize, out int count,string pdistrictCode)
        {
            string sql = @";WITH m(DistrictCode,NAME,ParentDistrictCode) AS(
	SELECT R.DistrictCode,R.NAME,R.ParentDistrictCode FROM dbo.HW_District R WHERE DistrictCode=@districtcode 
	UNION ALL SELECT child.DistrictCode,child.NAME,child.ParentDistrictCode FROM m parent, HW_District child WHERE parent.DistrictCode=child.ParentDistrictCode
)
select Top(@pageSize) * from  HW_Org b left join HW_OrgDetail c on b.OrgCode=c.OrgCode
                        INNER join m d on b.DistrictCode=d.DistrictCode LEFT join  m e on d.ParentDistrictCode=e.DistrictCode
                        where b.OrgCode not in (select TOP(@pageSize*@pageIndex) OrgCode from HW_Org y 
                        INNER JOIN m z ON y.DistrictCode=z.DistrictCode order by OrgCode)order by b.OrgCode
                        ";
            SqlParameter[] sp = new SqlParameter[]
            {       
				new SqlParameter("@pageIndex",pageIndex),
				new SqlParameter("@pageSize",pageSize),
				new SqlParameter("@districtCode",pdistrictCode),
            };
             SqlParameter[] sp2 = new SqlParameter[]
            {       
				new SqlParameter("@districtCode",pdistrictCode),
            };
            string sql2=@"
;WITH m(DistrictCode,NAME,ParentDistrictCode) AS(
	SELECT R.DistrictCode,R.NAME,R.ParentDistrictCode FROM dbo.HW_District R WHERE DistrictCode=@districtcode 
	UNION ALL SELECT child.DistrictCode,child.NAME,child.ParentDistrictCode FROM m parent, HW_District child WHERE parent.DistrictCode=child.ParentDistrictCode
)
select count(XX_UserName) from HW_OrgUser x inner join HW_Org y on x.OrgCode=y.OrgCode inner join m on y.DistrictCode=m.DistrictCode";
            count=(int)SqlHelper.ExecuteScalar(WebConn.connString,CommandType.Text,sql2,sp2);
            DataTable result = SqlHelper.ExecuteDataset(WebConn.connString,CommandType.Text,sql,sp).Tables[0];
            return result;
        }
        //public DataTable Search(int pageIndex, int pageSize, out int count, string searchString)
        //{
//            string sql=@";WITH m(DistrictCode,NAME,ParentDistrictCode) AS(
//	SELECT R.DistrictCode,R.NAME,R.ParentDistrictCode FROM dbo.HW_District R WHERE DistrictCode=@districtcode 
//	UNION ALL SELECT child.DistrictCode,child.NAME,child.ParentDistrictCode FROM m parent, HW_District child WHERE parent.DistrictCode=child.ParentDistrictCode
//)
//select Top(@pageSize) * from HW_OrgUser a inner join HW_Org b on a.OrgCode=b.OrgCode left join HW_OrgDetail c on a.OrgCode=c.OrgCode
//                        inner join m d on b.DistrictCode=d.DistrictCode inner join  m e on d.ParentDistrictCode=e.DistrictCode
//                        where (a.CNName like '%@searchString%' or b.OrgName like '%@searchString%') a.XX_UserName not in (select TOP(@pageSize*@pageIndex) XX_UserName from HW_OrgUser x inner join HW_Org y on
//                        x.OrgCode=y.OrgCode order by x.XX_UserName)order by XX_UserName
//                        "
//                         where (a.CNName like '%@searchString%' or b.OrgName like '%@searchString%')
        //}
        public HW_OrgUser GetByUserName(string userName)
        {
            string sql = @"select * from HW_OrgUser a inner join HW_Org b on a.OrgCode=b.OrgCode
                           left join HW_OrgDetail c on b.OrgCode=c.OrgCode where a.XX_UserName=@userName";
            SqlParameter[] sp = new SqlParameter[]{
                new SqlParameter("@userName",userName),
            };
            SqlDataReader reader = SqlHelper.ExecuteReader(WebConn.connString, CommandType.Text, sql, sp);
            HW_OrgUser orgUser = Mapper(reader);
            //HW_OrgService orgService = new HW_OrgService();
            //HW_Org org = orgService.Mapper(reader);
            //HW_OrgDetailService detailService = new HW_OrgDetailService();
            //HW_OrgDetail orgDetail = detailService.Mapper(reader);

            //orgUser.hw_Org = org;
            //orgUser.hw_Org.hw_OrgDetail = orgDetail;
            //DataSet ds= SqlHelper.ExecuteDataset(WebConn.connString, CommandType.Text, sql, sp);
            //return ds.Tables[0];
            return orgUser;
        }
        public DataTable Search(string district, string searchString)
        {
            string sql = @"
                   ;WITH m(DistrictCode,NAME,ParentDistrictCode) AS(
	SELECT R.DistrictCode,R.NAME,R.ParentDistrictCode FROM dbo.HW_District R WHERE DistrictCode=@districtcode 
	UNION ALL SELECT child.DistrictCode,child.NAME,child.ParentDistrictCode FROM m parent, HW_District child WHERE parent.DistrictCode=child.ParentDistrictCode
)
select * from HW_OrgUser a full join HW_Org b on a.OrgCode=b.OrgCode left join HW_OrgDetail c on a.OrgCode=c.OrgCode
                        INNER join m d on b.DistrictCode=d.DistrictCode LEFT join  m e on d.ParentDistrictCode=e.DistrictCode
                        where b.OrgName like @searchString or b.OrgCode like @searchString order by a.XX_UserName
                        ";
            SqlParameter[] sp = new SqlParameter[]
            {   
                new SqlParameter("@districtcode",district),
				new SqlParameter("@searchString","%"+searchString+"%"),
            };
            return SqlHelper.ExecuteDataset(WebConn.connString, CommandType.Text, sql, sp).Tables[0];
        }
        public int ResetPassword(SqlTransaction tans,string username, string password)
        {
            int result = 0;
            string sql = "update HW_OrgUser set  XX_Pass = @XX_Pass where XX_UserName = @XX_UserName";
            SqlParameter[] sp = new SqlParameter[]
            {       
				new SqlParameter("@XX_UserName",username),
				
				new SqlParameter("@XX_Pass",password),
				
            };
            result = SqlHelper.ExecuteNonQuery(tans, CommandType.Text, sql, sp);
            return result;
        }
        #endregion
                
    }
}
